package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsorder.entity.OrderWorkshopDtl;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 车间请购单详情表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-02-02
 */
@Mapper
@Component
public interface OrderWorkshopDtlMapper extends BaseMapper<OrderWorkshopDtl> {

    @Select("SELECT UUID AS \"uuid\",\n" +
            "  SPMS_WS_ID AS \"spmsWsId\",\n" +
            "  ROW_ID AS \"rowId\",\n" +
            "  MATERIAL_NO AS \"materialNo\",\n" +
            "  MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  REQUEST_QTY AS \"requestQty\",\n" +
            "  APPROVAL_QTY AS \"approvalQty\",\n" +
            "  BASIC_UNIT AS \"basicUnit\",\n" +
            "  BRAND AS \"brand\",\n" +
            "  ROW_STATUS AS \"rowStatus\",\n" +
            "  ROW_COMMENT AS \"rowComment\",\n" +
            "  DEVICE_ID AS \"deviceId\",\n" +
            "  DEVICE_CATEGORY AS \"deviceCategory\",\n" +
            "  DEVICE_TYPE AS \"deviceType\",\n" +
            "  MATERIAL_CATEGORY AS \"materialCategory\",\n" +
            "  LONG_CONTRACT_CODE AS \"longContractCode\"\n" +
            "FROM ORDER_WORKSHOP_DTL owsd\n" +
            "WHERE owsd.SPMS_WS_ID = #{spmsWsId} ORDER BY owsd.ROW_ID ASC \n")
    List<Map<String, Object>> getOrderWsDtlList(@Param("spmsWsId") String spmsWsId);

    @Select("SELECT OWSD.UUID AS \"uuid\",\n" +
            "       OWSD.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       OWSD.ROW_ID AS \"rowId\",\n" +
            "       OWS.WERKS AS \"werks\",\n" +
            "       OWSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       OWSD.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       OWSD.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       OWSD.REQUEST_QTY AS \"requestQty\",\n" +
            "       OWSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       OWSD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OWSD.BRAND AS \"brand\",\n" +
            "       OWSD.ROW_STATUS AS \"rowStatus\",\n" +
            "       OWSD.ROW_COMMENT AS \"rowComment\",\n" +
            "       OWSD.MATERIAL_CATEGORY AS \"materialCategory\",\n" +
            "       (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "       (CASE WHEN RE.QUANTITY IS NULL THEN 0 ELSE RE.QUANTITY END) AS \"inventoryQuantity\",\n" +
            "       (CASE WHEN WAY.WAY_QUANTITY IS NULL THEN 0 ELSE WAY.WAY_QUANTITY END) AS \"wayQuantity\"\n" +
            "FROM ORDER_WORKSHOP_DTL OWSD\n" +
            "    LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON OWSD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SAFETY_INVENTORY SI ON SI.WERKS = OWS.WERKS AND OWSD.MATERIAL_NO = SI.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(QUANTITY) AS \"QUANTITY\"\n" +
            "        FROM WHS_STORAGE_INVENTORY WSI\n" +
            "            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "    ) RE ON RE.WERKS = OWS.WERKS AND RE.MATERIAL_NO = OWSD.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT T.WERKS, T.MATERIAL_NO, SUM(T.DOMESTIC_QTY_RECEIVED) AS \"WAY_QUANTITY\"\n" +
            "        FROM (\n" +
            "             SELECT SUBSTR(SBND.DEMAND_NO, 0, 4) AS \"WERKS\",\n" +
            "                    SBND.MATERIAL_NO,\n" +
            "                    SBND.DOMESTIC_QTY_RECEIVED\n" +
            "             FROM (\n" +
            "                 SELECT MAX(LOG_AUTO_ID) AS \"ID\", L.BOX_NOTE\n" +
            "                 FROM WHS_OPERATE_LOG L\n" +
            "                 WHERE L.BOX_NOTE IS NOT NULL AND L.WERKS = 'CN01'\n" +
            "                       AND L.OPERATION_TYPE IN ('3', '16', '22')\n" +
            "                   AND L.COMMENTS != '自管仓备件入库撤销下架' GROUP BY BOX_NOTE\n" +
            "                 ) R LEFT JOIN SAP_BOX_NOTE_DTL SBND ON R.BOX_NOTE = SBND.BOX_NOTE\n" +
            "             WHERE SBND.DEMAND_NO IS NOT NULL\n" +
            "                   ) T\n" +
            "        GROUP BY T.WERKS, T.MATERIAL_NO\n" +
            "    ) WAY ON OWS.WERKS = WAY.WERKS AND OWSD.MATERIAL_NO = WAY.MATERIAL_NO\n" +
            "WHERE OWSD.SPMS_WS_ID = #{spmsWsId} ORDER BY OWSD.ROW_ID ASC ")
    /** 获取一个车间请购单明细信息，包含 在库库存、在途、安全库存等信息 */
    List<Map<String, Object>> getOrderWsDtlWithInventory(@Param("spmsWsId") String spmsWsId);

    @Select("<script>" +
            "SELECT ROWNUM AS \"id\", \n" +
            "       WD.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       WD.WERKS AS \"werks\",\n" +
            "       OWD.UUID AS \"uuid\",\n" +
            "       OWD.ROW_ID AS \"rowId\",\n" +
            "       OWD.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       OWD.REQUEST_QTY AS \"requestQty\",\n" +
            "       OWD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.BRAND AS \"brand\",\n" +
            "       OWD.ROW_STATUS AS \"rowStatus\",\n" +
            "       OWD.ROW_COMMENT AS \"rowComment\",\n" +
            "       OWD.MATERIAL_CATEGORY AS \"materialCategory\",\n" +
            "       (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "       (CASE WHEN RE.QUANTITY IS NULL THEN 0 ELSE RE.QUANTITY END) AS \"inventoryQuantity\",\n" +
            "       (CASE WHEN WAY.WAY_QUANTITY IS NULL THEN 0 ELSE WAY.WAY_QUANTITY END) AS \"wayQuantity\"\n" +
            "FROM ORDER_WORKSHOP_DTL OWD\n" +
            "    LEFT JOIN ORDER_WORKSHOP WD ON OWD.UUID = WD.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON OWD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SAFETY_INVENTORY SI ON WD.WERKS = SI.WERKS AND OWD.MATERIAL_NO = SI.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(QUANTITY) AS \"QUANTITY\"\n" +
            "        FROM WHS_STORAGE_INVENTORY WSI\n" +
            "            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "    ) RE ON RE.WERKS = WD.WERKS AND RE.MATERIAL_NO = OWD.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT T.WERKS, T.MATERIAL_NO, SUM(T.DOMESTIC_QTY_RECEIVED) AS \"WAY_QUANTITY\"\n" +
            "        FROM (\n" +
            "            SELECT SUBSTR(SBND.DEMAND_NO, 0, 4) AS \"WERKS\",\n" +
            "                 SBND.MATERIAL_NO,\n" +
            "                 SBND.DOMESTIC_QTY_RECEIVED\n" +
            "            FROM (\n" +
            "                 SELECT MAX(LOG_AUTO_ID) AS \"ID\",\n" +
            "                        L.BOX_NOTE\n" +
            "                 FROM WHS_OPERATE_LOG L\n" +
            "                 WHERE L.BOX_NOTE IS NOT NULL AND L.WERKS = 'CN01'\n" +
            "                       AND L.OPERATION_TYPE IN ('3', '16', '22')\n" +
            "                   AND L.COMMENTS != '自管仓备件入库撤销下架' GROUP BY BOX_NOTE\n" +
            "                ) R LEFT JOIN SAP_BOX_NOTE_DTL SBND ON R.BOX_NOTE = SBND.BOX_NOTE\n" +
            "            WHERE SBND.DEMAND_NO IS NOT NULL\n" +
            "                  ) T\n" +
            "        GROUP BY T.WERKS, T.MATERIAL_NO\n" +
            "    ) WAY ON WD.WERKS = WAY.WERKS AND OWD.MATERIAL_NO = WAY.MATERIAL_NO\n" +
            "WHERE WD.STATUS != '0'\n" +
            "      AND WD.SPMS_WS_ID IN \n" +
            "      <foreach item='item' index='index' collection='spmsWsIds' open='(' separator=',' close=')'>\n" +
            "           #{item} \n" +
            "       </foreach>" +
            "ORDER BY WD.SPMS_WS_ID, OWD.ROW_ID " +
            "</script>")
    /** 获取多个车间请购单明细信息，包含 在库库存、在途、安全库存等信息 */
    List<Map<String, Object>> getOrderWorkshopDtlList(@Param("spmsWsIds") List<String> spmsWsIds);

}
